package com.redmoon.oa.util;

import cn.js.fan.base.ObjectDb;
import cn.js.fan.db.*;
import cn.js.fan.util.ErrMsgException;
import cn.js.fan.util.StrUtil;
import cn.js.fan.web.Global;
import com.cloudwebsoft.framework.db.JdbcTemplate;
import com.cloudwebsoft.framework.util.LogUtil;
import com.redmoon.oa.sys.SysUtil;
import org.apache.commons.lang3.StringUtils;

import javax.servlet.jsp.JspWriter;
import java.io.IOException;
import java.sql.*;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Vector;
import java.sql.*;
import java.util.*;

public class TransmitDataToPostgre {
    private static final String MYSQL_URL = "jdbc:mysql://127.0.0.1:3311/redmoonoa8";
    private static final String MYSQL_USER = "root";
    private static final String MYSQL_PASSWORD = "myoa888";

    private static final String POSTGRESQL_URL = "jdbc:postgresql://127.0.0.1:5432/postgres";
    private static final String POSTGRESQL_USER = "postgres";
    private static final String POSTGRESQL_PASSWORD = "myoa888";

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        // syncIndexForPOostGre();
        syncDefaultValuesForPostGre();
    }

    public static void syncIndexForPOostGre() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Class.forName("org.postgresql.Driver");

        try (Connection mysqlConn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
             Connection pgsqlConn = DriverManager.getConnection(POSTGRESQL_URL, POSTGRESQL_USER, POSTGRESQL_PASSWORD)) {

            DatabaseMetaData mysqlMeta = mysqlConn.getMetaData();
            Statement pgsqlStmt = pgsqlConn.createStatement();

            // ResultSet tablesRs = mysqlMeta.getTables(null, null, "%", new String[]{"TABLE"});
            ResultSet tablesRs = mysqlMeta.getTables(mysqlConn.getCatalog(), "root", null, new String[]{"TABLE"});

            Map<String, Integer> map = new HashMap<>();
            while (tablesRs.next()) {
                String tableName = tablesRs.getString("TABLE_NAME");

                LogUtil.getLog(TransmitDataToPostgre.class).info("tableName=" + tableName);

                /*if (!"document".equals(tableName)) {
                    continue;
                }
                */
                List<String> primaryKeyColumns = new ArrayList<>();
                List<String> indexKeyColumns = new ArrayList<>();
                List<String> columnComments = new ArrayList<>();
                List<String> uniqueindexKeyColumns = new ArrayList<>();

                // 重建表结构
                /*StringBuilder createTableSql = new StringBuilder("CREATE TABLE ");
                createTableSql.append(tableName).append(" (\n");

                ResultSet columnsRs = mysqlMeta.getColumns(null, null, tableName, null);

                while (columnsRs.next()) {
                    String columnName = columnsRs.getString("COLUMN_NAME");
                    int dataType = columnsRs.getInt("DATA_TYPE");
                    String typeName = mapColumnType(dataType);
                    int size = columnsRs.getInt("COLUMN_SIZE");
                    boolean isNullable = columnsRs.getBoolean("NULLABLE");
                    String columnDef = columnsRs.getString("COLUMN_DEF");
                    String columnComment = columnsRs.getString("REMARKS");

                    createTableSql.append("\t").append(columnName).append(' ').append(typeName);
                    if (!"INTEGER".equalsIgnoreCase(typeName)
                            &&!"int4".equalsIgnoreCase(typeName)
                            &&!"int8".equalsIgnoreCase(typeName)
                            &&!"smallint".equalsIgnoreCase(typeName)
                            &&!"bigint".equalsIgnoreCase(typeName)
                            &&!"text".equalsIgnoreCase(typeName)){
                        if (size > 0) {
                            createTableSql.append('(').append(size).append(')');
                        }
                    }

                    if (!isNullable) {
                        createTableSql.append(" NOT NULL");
                    }

                    if (columnDef != null) {
                        if ("INTEGER".equalsIgnoreCase(typeName)
                                ||"int4".equalsIgnoreCase(typeName)
                                ||"int8".equalsIgnoreCase(typeName)
                                ||"smallint".equalsIgnoreCase(typeName)
                                ||"bigint".equalsIgnoreCase(typeName)
                                ||"NUMERIC".equalsIgnoreCase(typeName)){
                            createTableSql.append(" DEFAULT ").append(columnDef);
                        }else if("TIMESTAMP".equalsIgnoreCase(typeName)){
                            createTableSql.append(" DEFAULT ").append("TO_TIMESTAMP('2024-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')");
                        }else{
                            createTableSql.append(" DEFAULT ").append("'" + escapeSingleQuotes(columnDef) + "'");
                        }
                    }

                    columnComments.add(addColumnComment(tableName, columnName, columnComment, pgsqlStmt));

                    createTableSql.append(",\n");

                    // 检查是否为主键
                    ResultSet pkRs = mysqlMeta.getPrimaryKeys(null, null, tableName);
                    while (pkRs.next() && pkRs.getString("COLUMN_NAME").equals(columnName)) {
                        primaryKeyColumns.add(columnName);
                    }
                    pkRs.close();

                }

                // 移除最后一个逗号和换行符
                createTableSql.setLength(createTableSql.length() - 2);

                // 添加主键约束
                if (!primaryKeyColumns.isEmpty()) {
                    createTableSql.append(", \n    PRIMARY KEY (")
                            .append(String.join(",", primaryKeyColumns))
                            .append(")");
                }

                createTableSql.append("\n);");
                System.out.println(createTableSql);*/
                //pgsqlStmt.executeUpdate(createTableSql.toString());

                try {
                    // 处理索引及联合索引
                    StringBuilder sbIndex = new StringBuilder(30);
                    Map<String, List<String>> unIndexMap = new HashMap<>();
                    ResultSet uniqueindexSet = mysqlMeta.getIndexInfo(null, null, tableName, false, false);
                    while (uniqueindexSet.next()) {
                        // document 表中的索引对应的字段为 examed，但是取出后为 exame ???
                        String indexName = uniqueindexSet.getString("INDEX_NAME"); // 索引名称
                        String columnName = uniqueindexSet.getString("COLUMN_NAME"); // 索引列名
                        /*boolean isNonUnique = uniqueindexSet.getBoolean("NON_UNIQUE"); // 是否非唯一索引（0表示唯一索引，1表示非唯一索引）
                        System.out.println("indexName="+indexName+",columnName="+columnName+",isNonUnique="+isNonUnique);*/
                        if ("PRIMARY".equalsIgnoreCase(indexName)) {
                            sbIndex.append("ALTER TABLE \"public\".\"" + tableName + "\" ADD PRIMARY KEY (\"" + columnName.toLowerCase() + "\");");
                            continue;
                        }
                        List<String> indexCols = unIndexMap.get(indexName);
                        if (indexCols == null) {
                            indexCols = new ArrayList<>();
                        }
                        if (!indexCols.contains(columnName)) {
                            indexCols.add(columnName);
                        }
                        unIndexMap.put(indexName, indexCols);
                        uniqueindexKeyColumns.add(columnName);
                    }
                    for (Map.Entry<String, List<String>> entry : unIndexMap.entrySet()) {
                        // navicat 在传输的时候已经将表名、字段都转为小写了
                        String indexName = entry.getKey().toLowerCase();
                        if (!map.containsKey(indexName)) {
                            map.put(indexName, 0);
                        } else {
                            int k = map.get(indexName) + 1;
                            map.put(indexName, k);
                            indexName += k;
                        }
                        List<String> list = entry.getValue();
                        if (list.size() > 1) {
                            // 联合索引
                            System.out.println("CREATE INDEX " + indexName + " ON " + tableName + "(" + String.join(",", list) + ");");
                            sbIndex.append("CREATE INDEX " + indexName + " ON " + tableName + "(" + String.join(",", list) + ");");
                        } else {
                            // 普通索引
                            if ((indexName.startsWith("uni") && !indexName.contains("unit_code")) || indexName.startsWith("uk_")) {
                                System.out.println("CREATE UNIQUE INDEX " + indexName + " ON " + tableName + "(" + list.get(0) + ");");
                                sbIndex.append("CREATE UNIQUE INDEX " + indexName + " ON " + tableName + "(" + list.get(0) + ");");
                            } else {
                                System.out.println("CREATE INDEX " + indexName + " ON " + tableName + "(" + list.get(0) + ");");
                                sbIndex.append("CREATE INDEX " + indexName + " ON " + tableName + "(" + list.get(0) + ");");
                            }
                        }
                    }
                    uniqueindexSet.close();

                    pgsqlStmt.executeUpdate(sbIndex.toString());
                } catch (Exception e) {
                    LogUtil.getLog(TransmitDataToPostgre.class).error(e);
                }

                // 处理外键索引
                /*ResultSet foreignKeysRS = mysqlMeta.getImportedKeys(null, null, tableName);
                while (foreignKeysRS.next()) {
                    String fkName = foreignKeysRS.getString("FK_NAME"); // 外键约束名称
                    String fkColumnName = foreignKeysRS.getString("FKCOLUMN_NAME"); // 本表中对应的外键列名
                    String pkTableName = foreignKeysRS.getString("PKTABLE_NAME"); // 主键所在的表名
                    String pkColumnName = foreignKeysRS.getString("PKCOLUMN_NAME"); // 主键列名
                    System.out.println("Foreign Key: " + fkName);
                    System.out.println("From Column: " + fkColumnName + " in Table " + tableName);
                    System.out.println("References Column: " + pkColumnName + " in Table " + pkTableName);
                }
                foreignKeysRS.close();*/

                //字段注释
                if (!columnComments.isEmpty()) {
                    for (String comment : columnComments) {
                        System.out.println(comment);
                    }
                }
                // 获取表注释并添加
                String tableComment = getTableComment(tableName, mysqlMeta);
                System.out.println(addTableComment(tableName, tableComment, pgsqlStmt));
                System.out.println("\n");
                // 插入数据
                //copyTableData(tableName, mysqlConn, pgsqlConn);
            }

            tablesRs.close();
        }
    }

    public static void syncDefaultValuesForPostGre()  throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Class.forName("org.postgresql.Driver");

        try (Connection mysqlConn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
             Connection pgsqlConn = DriverManager.getConnection(POSTGRESQL_URL, POSTGRESQL_USER, POSTGRESQL_PASSWORD)) {

            DatabaseMetaData mysqlMeta = mysqlConn.getMetaData();
            Statement pgsqlStmt = pgsqlConn.createStatement();

            // ResultSet tablesRs = mysqlMeta.getTables(null, null, "%", new String[]{"TABLE"});
            ResultSet tablesRs = mysqlMeta.getTables(mysqlConn.getCatalog(), "root", null, new String[]{"TABLE"});

            while (tablesRs.next()) {
                String tableName = tablesRs.getString("TABLE_NAME");

                /*if (!"flow_action".equals(tableName)) {
                    continue;
                }*/
                LogUtil.getLog(TransmitDataToPostgre.class).info("tableName=" + tableName);

                try {
                    DatabaseMetaData dmd = mysqlConn.getMetaData();
                    ResultSet rsCol = dmd.getColumns(null, null, tableName, null);
                    StringBuilder sb = new StringBuilder(30);
                    while (rsCol.next()) {
                        String col = rsCol.getObject(4).toString().toLowerCase();
                        String defaultValue = rsCol.getString("COLUMN_DEF");
                        if (!StrUtil.isEmpty(defaultValue)) {
                            // LogUtil.getLog(TransmitDataToPostgre.class).info("tableName=" + tableName + " col=" + col + " defaultValue=" + defaultValue);
                            int dataType = rsCol.getInt("DATA_TYPE");
                            String typeName = TransmitDataToPostgre.mapColumnType(dataType);
                            if ("timestamp".equalsIgnoreCase(typeName)) {
                                if (!defaultValue.startsWith("0000-00-00")) {
                                    /*String sql = "ALTER TABLE \"public\".\"" + tableName + "\" ALTER COLUMN \"" + col + "\" SET DEFAULT " + SQLFilter.getDateStr(defaultValue) + ";";
                                    pgsqlStmt.executeUpdate(sql);*/
                                }
                            }
                            else if (!("INTEGER".equalsIgnoreCase(typeName)
                                    || "int4".equalsIgnoreCase(typeName)
                                    || "int8".equalsIgnoreCase(typeName)
                                    || "smallint".equalsIgnoreCase(typeName)
                                    || "bigint".equalsIgnoreCase(typeName))) {
                                defaultValue = StrUtil.sqlstr(defaultValue);
                                sb.append("ALTER TABLE \"public\".\"" + tableName + "\" ALTER COLUMN \"" + col + "\" SET DEFAULT " + defaultValue + ";\n");
                            } else {
                                sb.append("ALTER TABLE \"public\".\"" + tableName + "\" ALTER COLUMN \"" + col + "\" SET DEFAULT " + defaultValue + ";\n");
                            }
                        }
                    }
                    // LogUtil.getLog(TransmitDataToPostgre.class).info(sb.toString());
                    pgsqlStmt.executeUpdate(sb.toString());
                } catch (Exception e) {
                    LogUtil.getLog(TransmitDataToPostgre.class).error(e);
                }
            }

            tablesRs.close();
        }
    }

    private static String addTableComment(String tableName, String comment, Statement stmt) throws SQLException {
        /*if (comment != null && !comment.isEmpty()) {
            stmt.executeUpdate("COMMENT ON TABLE " + tableName + " IS '" + escapeSingleQuotes(comment) + "'");
        }*/
        return "COMMENT ON TABLE " + tableName + " IS '" + escapeSingleQuotes(comment) + "';";
    }

    private static String addColumnComment(String tableName, String columnName, String comment, Statement stmt) throws SQLException {
        return "COMMENT ON COLUMN " + tableName + "." + columnName + " IS '" + escapeSingleQuotes(comment) + "';";
        /*if (comment != null && !comment.isEmpty()) {
            stmt.executeUpdate("COMMENT ON COLUMN " + tableName + "." + columnName + " IS '" + escapeSingleQuotes(comment) + "'");
        }*/
    }

    private static String getTableComment(String tableName, DatabaseMetaData meta) throws SQLException {
        ResultSet rs = meta.getTables(null, null, tableName, null);
        if (rs.next()) {
            return rs.getString("REMARKS");
        }
        return "";
    }

    public static String mapColumnType(int mysqlDataType) {
        // 这里仅提供一种简单的映射方式，实际情况可能更复杂
        switch (mysqlDataType) {
//            case Types.BIT:
//                return "BOOLEAN";
            case Types.TINYINT:
                return "SMALLINT";
            case Types.BIGINT:
                return "BIGINT";
            case Types.INTEGER:
                return "INTEGER";
            case Types.DATE:
                return "timestamp";
            case Types.DOUBLE:
                return "NUMERIC";
            case Types.NUMERIC:
                return "NUMERIC";
            case Types.DECIMAL:
                return "NUMERIC";
            case Types.LONGVARCHAR:
                return "text";
            case Types.TIMESTAMP:
                return "TIMESTAMP";
            case Types.BLOB:
                return "BYTEA";
            // 其他类型...
            default:
                return "varchar";
        }
    }

    private static void copyTableData(String tableName, Connection mysqlConn, Connection pgsqlConn) throws SQLException {
        String selectAllDataSql = "SELECT * FROM " + tableName;
        PreparedStatement mysqlPs = mysqlConn.prepareStatement(selectAllDataSql);
        ResultSet dataRs = mysqlPs.executeQuery();
        ResultSetMetaData metaData = dataRs.getMetaData();
        StringBuilder insertSql = new StringBuilder("INSERT INTO ").append(tableName).append(" VALUES (");

        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            insertSql.append("?");
            if (i < metaData.getColumnCount()) {
                insertSql.append(",");
            }
        }
        insertSql.append(")");

        PreparedStatement pgsqlPs = pgsqlConn.prepareStatement(insertSql.toString());
        while (dataRs.next()) {
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                pgsqlPs.setObject(i, dataRs.getObject(i));
            }
            pgsqlPs.addBatch();
        }
        pgsqlPs.executeBatch();
    }

    private static String escapeSingleQuotes(String str) {
        return str.replace("'", "''");
    }
}